package com.huayi.website.repository;

import com.huayi.website.entity.NewsCategoryEntity;
import com.huayi.website.entity.NewsEntity;
import com.huayi.website.entity.ProductEntity;
import com.huayi.website.utils.PagingResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class NewsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<NewsCategoryEntity> getAllCategory(){
        return jdbcTemplate.query("SELECT * FROM "+Tables.NewsCategory+" ORDER BY id desc",new BeanPropertyRowMapper<>(NewsCategoryEntity.class));
    }

    public NewsCategoryEntity getCategoryById(Integer id) {
        try{
            return jdbcTemplate.queryForObject("SELECT * FROM "+Tables.NewsCategory +" WHERE id=?",new BeanPropertyRowMapper<>(NewsCategoryEntity.class),id);
        }catch (Exception e){
            return null;
        }
    }

    @Transactional
    public Integer editCategory(Integer categoryId, String name, String content, Integer sort, String image){
        //如果存在，更新
        if(categoryId==null || categoryId==0){
            String goodsSQL = "INSERT INTO " + Tables.NewsCategory + "(name,image,sort,content) VALUE (?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(goodsSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, name);
                    ps.setString(2, image);
                    ps.setInt(3, sort);
                    ps.setString(4, content);

                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            return id;
        }else{
            //更新
            String goodsSQL = "UPDATE " + Tables.NewsCategory + "SET name=?,image=?,sort=?,content=? WHERE id=?";
            jdbcTemplate.update(goodsSQL,name,image,sort,content,categoryId);
            return categoryId;
        }
    }

    @Transactional
    public void deleteCategory(Integer id) {
        try {
            jdbcTemplate.update("DELETE FROM " + Tables.NewsCategory + " where id=?", id);

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }
    @Transactional
    public void deleteNews(Integer id) {
        try {
            jdbcTemplate.update("DELETE FROM " + Tables.News + " where id=?", id);

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }

    /**
     * 添加新闻
     * @param news
     * @return
     */
    public Integer editNews(NewsEntity news){
        if(news.getId()==null || news.getId()<=0) {
            String sql = "INSERT INTO " + Tables.News + "(title,categoryId,keyword,description,source,sort,content,img_url,`language`) VALUE (?,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, news.getTitle());
                    ps.setInt(2, news.getCategoryId());
                    ps.setString(3, news.getKeyword());
                    ps.setString(4, news.getDescription());
                    ps.setString(5, "");
                    ps.setInt(6, news.getSort());
                    ps.setString(7, news.getContent());
                    ps.setString(8, news.getImgUrl());
                    ps.setInt(9, news.getLanguage());
                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            return id;
        }else{
            //更新
            String sql = "UPDATE "+Tables.News+" SET title=?,categoryId=?,keyword=?,description=?,sort=?,content=?,img_url=?,`language`=? WHERE id=? ";
            jdbcTemplate.update(sql,news.getTitle(),news.getCategoryId(),news.getKeyword(),news.getDescription(),news.getSort(),news.getContent(),news.getImgUrl(),news.getLanguage(),news.getId());
            return news.getId();
        }
    }

    /**
     * 分页获取新闻
     * @param pageIndex
     * @param pageSize
     * @param title
     * @param categoryId
     * @return
     */
    public PagingResponse<NewsEntity> getAllList(int pageIndex, int pageSize, String title, Integer categoryId,Integer language){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS n.*,nc.name as categoryName FROM").append(Tables.News).append(" as n ");
        sb.append(" LEFT JOIN ").append(Tables.NewsCategory).append(" as nc on nc.id = n.categoryId ");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(title)) {
            sb.append(" AND n.title LIKE ? ");
            params.add("%" + title + "%");
        }
        if (categoryId!=null && categoryId > 0) {
            sb.append(" AND n.categoryId =? ");
            params.add(categoryId);
        }
        if(!StringUtils.isEmpty(language)){
            sb.append(" AND n.language =? ");
            params.add(language);
        }
        sb.append(" ORDER BY n.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(NewsEntity.class),params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }

    public NewsEntity getNewsById(Integer id){
        try{
            return jdbcTemplate.queryForObject("SELECT * FROM "+Tables.News+" WHERE id=?",new BeanPropertyRowMapper<>(NewsEntity.class),id);
        }catch (Exception e){
            return null;
        }
    }

}
